Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filters in the Power BI service. All Power BI reports will now automatically retain the filters, slicers, and other data view changes that you have made. You no longer need to spend your valuable time slicing and dicing your report and repeating the same steps each time you return to the service. With this feature, you will be able to pick up right where you left off last time and quickly get to your insights! Also, if you want to reset all the filter to the state when the report was publishing, this feature will allow you to do that. Enabling Persistent Filter: To see persistent filters in action, simply head to any Power BI report that you have view or edit access to. You will notice a new button on the top bar that says, “Reset to default”. By default, this is disabled. It essentially means that you are viewing the author’s published view of the report and have not made any changes. Once you have modified the report to the view that you like, will activate the Result to default button, and will allow you to go back to default state when it was published. Click on reset to default And you are done.  Report will be reset to the default published state. Disabling persistent Filter for report: Persistent filter is turned on by default for all reports. If you want to disable the feature for the report, then you need to use the latest power BI desktop version that is released on Feb 2018. You need to navigate to : Power BI Desktop > Options > Report Settings > Persistent Filter. This is amazing feature that allows users to interact with report as they want. Try it out and let us know if there is any issue in comments below.

Introduction: In this blog you will see how you can use power BI as existing dataset to create the report in Power BI desktop. Service Live Connection: You can establish connection to a shared dataset in the Power BI service, and create many different reports from the same dataset. This means you can create your perfect data model in Power BI Desktop, publish it to the Power BI service, then you and others can create multiple different reports (in separate. pbix files) from that same, common data model. This feature is called Power BI service Live connection. To create Shared data set you need to, create a dataset and report and publish it in a workspace which is common to all. Select the workspace that is shared and where the report needs to be deployed. Report will start to Puclish to workspace. You will get below confirmation, when the report is sucecssfully published. Establish a Power BI service live connection to the published dataset If you’re not signed in to Power BI, you’ll be prompted to do so. Once logged in, you’re presented with a window that shows which workspaces you’re a member of, and you can select which workspace contains the dataset to which you want to establish a Power BI service live connection. Click on load and the dataset will be loaded and you can create the reports and publish it. Below are some known limitations to this as well: Read-only members of a workspace cannot connect to datasets from Power BI Desktop. Only users who are part of the same Power BI service workspace can connect to a published dataset using the Power BI service live connection. Users can (and often do) belong to more than one workspace. Try it out and put you question below if there is anything.

Introduction: In this blog you will see how we you can use content pack for D365 in Power BI. Content package that are created are easy for your team to find — they are all in AppSource. Because they’re part of Power BI, they leverage all the features of Power BI, including interactive data exploration, new visuals, Q&A, integration with other data sources, data refresh, and more. Steps: Login to your Power BI Account > Click on Get Data. Click on Services. Select the proper content pack from the AppSource. Click on Sales Analytics for Dynamics 365 > and click on “Get it now”. It will as about the CRM account, and the Fiscal Year End Month Number, fill in the details and click next. By default, it will use OAuth with the current account that is logged in. After successfully login, dashboard will start with pumping up with data. Importing data will take some time, and after successful login you will get the ready content package Dashboards and report. You are set and good to go, try other content pack as well. Currently there are 21 Content pack available for different flavour of D365, try them there are superb.

Introduction: This blog will help you through connecting power BI to your D365 environment. Steps: Open Power BI Desktop, click on Get data connection, and click on More… Go to Online Services > then select Dynamics 365 (Online) > then click on connect. It will ask you about Web API URL, this is not the CRM URL, it is the API URL that will allow you to communicate to you D365. You can locate the Web API in your Dynamics 365: Go to Dynamics365 > Go to Settings > Customizations. Select Developer resource inside Customizations. Inside the Developer Resource you can locate your Web API URL. Copy the Service Root URL and paste it in your Power BI Desktop. After adding the URL click on and click on the Organizational Account and click on Sign-in. After successful sign in, click on connect, you will see list of entities available for the login. Select the tables and get going, you are good to go now.

Introduction With the latest release of Power BI, new feature has been added to share the power BI reports as well. Yes, you heard it right, now we can share the Power BI Reports as well to teams and partners. How it works: Go to your workspace and open the report that needs to be shared. Click on the share option. Enter the email address and click on share, if the send email notification option is checked then notification will be send to users. Conclusion: This is a superb feature that is added to power bi, allowing organization to share reports as well with dashboards for detailed information.

Introduction: In this blog we will see how we can deploy SSIS integration package individually using file System Deployment. Steps: Convert the current project to package deployment model. After clicking the project deployment model, the screen will pop up with the compatibility check for all the packages with below screen, click ok if all is good. Below icon and name will be changes for the solution when you click Ok button from above screen. Create your package and test it in visual studio, save your package. Now we go for deployment of package: Capture the path for the package: “D:\PackageDeploy\Package.dtsx” Deploying Package to SQL Server and Configuring it to SQL Agent Job: Create New job Create the Steps in JOB Crete the Step. Select the Step type to SQL Server Integration Services Package Select the Package source type: File System as this is a package Deployment model. Provide the package path Note:  While providing the package path we need to make sure that SQL server can communicate to the path as this will be running inside the SQL server. Execute package and check the Job status: Conclusion: With the above steps you should be able to deploy the SSIS package using package deployment model, there are many more methods to deploy the package, like- by using manifest file, Project deployment models and so on, stay tuned for upcoming blogs.

With Power BI latest release, you can now hide the page in Power BI reports, this page is available while you are developing and when you hide it by right click on the package and selecting Hide Page, then that page will not be available in Reading mode. When you select the Hide page option then the option is checked as hide page. Page is marked as dull colour to show that it is hidden. When you publish this to your workspace then page will not be displayed in reading mode: So, you can technically hide page in your power bi report if you don’t want to use them, but if the user has rights to edit the report then he can change the settings.

TRIM Function has haunted SQL DBA for ages. If you have been using SQL Server for a while, you will totally agree with me over here. In this blog post we will see how the new feature TRIM of SQL Server 2017 works in few simple words. SQL Server DBAs and Developers have always dealt with SQL Strings and the leading and trailing spaces often makes them crazy. Data query may not be at it’s best if there are leading spaces around strings and which are not useful for data comparison and well as storage point of view. In the previous server versions developers used to two different functions LTRIM and RTRIM around the string to get necessary results. However now in SQL Server 2017 we have a new feature introduced which is TRIM(). This function works just like LTRIM and RTRIM together. When you run above script you can see that when we use function TRIM around the string it removes leading and trailing spaces. Trim function is combination for LTRIM & RTRIM, and only available in SQL server 2017. For earlier version, LTrim and RTrim is available.

With Excel 2016, you can publish your Excel workbooks right to your Power BI site, where you can create highly interactive reports and dashboards based on your workbook’s data. You can then share your insights with others in your organization. Requirements: 1. Before publishing to Power BI, workbook must be saved to OneDrive for Business. 2. Only Excel 2016 with an Office 365 subscription will see the experience to publish with local files. Excel 2016 standalone installation will still have the “Publish” only behaviour which requires the excel workbook be saved to OneDrive for Business or SharePoint Online. 3. The account should be same for Office, OneDrive for Business, and Power BI. 4. Empty workbook or a workbook that doesn’t have any Power BI supported content cannot be published. 5. Encrypted or password protected workbooks, or workbooks with Information Protection Management cannot be published. Steps: In Excel, select File > Publish (Local file publishing). When you select Publish, you will be able to select the workspace you want to publish to. This can be your personal or group workspace that you have access to. You’ll get two options on how to get your workbook into Power BI. Upload your workbook to Power BI: When you choose this option, your workbook will appear in Power BI just like it would in Excel Online. But, unlike Excel Online, you’ll have some great features to help you pin elements from your worksheets to dashboards. You can’t edit your workbook in when open in Power BI, but if you need to make some changes, you can select Edit, and then choose to edit your workbook in Excel Online or open it in Excel on your computer. Any changes you make are saved to the workbook on OneDrive. When you upload, no dataset is created in Power BI. Your workbook will appear in Reports, in your workspace navigation pane. Workbooks uploaded to Power BI have a special Excel icon, identifying them as Excel workbooks that have been uploaded. Choose this option if you only have data in worksheets, or you have PivotTables and Charts you want to see in Power BI. Using Upload from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Connect, Manage and View Excel in Power BI from Power BI in your browser. Export workbook data to Power BI: When you choose this option, any supported data in tables and/or a data model are exported into a new dataset in Power BI. If you have any Power View sheets, those will be re-created in Power BI as reports. You can continue editing your workbook. When your changes are saved, they’ll be synchronized with the dataset in Power BI, usually within about an hour. If you need more immediate gratification, you can just select Publish again, and your changes are exported right then and there. Any visualizations you have in reports and dashboards will be updated, too. Choose this option if you’ve used Get & Transform data or Power Pivot to load data into a data model, or if your workbook has Power View sheets with visualizations you want to see in Power BI. Using Export from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Export Excel data into Power BI from Power BI in your browser. Publishing: When you choose either option, Excel will sign in to Power BI with your current account, and then publish your workbook to your Power BI site. Keep an eye on the status bar in Excel. It shows how things are going. To keep the data live, save your workbook to OneDrive and create a ODataFeed connection. Visit this blog article for more information. Let us know if there is any issue while implementing or contact us.

Yes, you heard it right, you can import Microsoft Excel Workbook to Power BI and show you selected section in you powered dashboards, let’s see how it works, as Microsoft Excel is one of the most widely used business applications around. It’s also one of the most common ways to get your data into Power BI. Requirement: Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Workbooks must be saved as .xlsx or .xlsm file type and be under 1 GB. Raw Data Vs Range Tables: If your workbook has simple worksheets with ranges of data, to get the most out of your data in Power BI, be sure to format those ranges as tables. This way, when creating reports in Power BI, you’ll see named tables and columns in the Fields pane, making it much easier to visualize your data. Power View Sheets, Pivottables And Charts: How your PowerView sheets and PivotTables and charts appear, or not appear, in Power BI depends on where your workbook file is saved and how you choose to get it into Power BI. We’ll go into this more below. Data Types: Power BI supports the following data types: Whole Number, Decimal Number, Currency, Date, True/False, Text. Marking data as specific data types in Excel will improve the Power BI experience. Saving you Excel file local or OneDrive can make a difference: Local – If you save your workbook file to a local drive on your computer or another location in your organization, from Power BI you can load your file into Power BI. Your file will remain on your local drive, so the whole file isn’t really imported into Power BI. What really happens is a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset. If your workbook has any Power View sheets, those will appear in your Power BI site under Reports. Excel 2016 also has the Publish feature (under the File menu) which is discussed in our other blog. OneDrive – Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your work in Excel and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your workbook file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI. Two Ways to Import Excel workbook: 1. OneDrive 2. Import Excel data into Power BI Import or connect to an Excel workbook from Power BI 1. In Power BI, in the navigation pane, click Get Data. 2. In Files, click Get. 3. Find your file. 4. If your workbook file is on OneDrive or SharePoint – Team Sites, choose Import or Connect. Open the workbook and select he section that you want to pin to Dashboards. Try this yourself, it’s an amazing feature and allows us to use the standalone data points to show in Dashboards when even required. Feel free to contact us in case of any issue.